﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Configuration;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Web.Configuration;
using System.Data.SqlClient;

/// <summary>
/// Summary description for TourHotelManagement
/// </summary>
public class TourHotelManagement
{
    #region const declaration
    public const string TABLE_NAME = "Tour_Hotel";
    public const string TOURHOTELID = "tourHotelID";
    public const string TOURID = "tourID";
    public const string HOTELID = "hotelID";
    public const string NUMROOM = "numRoom";
    public const string DEPARTDATE = "departDate";
    public const string RETURNDATE = "returnDate";
    public const string INTERVALTIME = "intervalTime";
    #endregion


	public TourHotelManagement()
	{
	}

    public static bool AddTourHotel(int tourID, int hotelID, int numRoom, DateTime departDate, DateTime returnDate, float intervalTime)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();
     
        string sqlQuery = "INSERT INTO Tour_Hotel Values(@tourID,@hotelID,@numRoom,@departDate,@returnDate,@intervalTime);SELECT scope_identity()";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@tourID", tourID);
        command.Parameters.AddWithValue("@hotelID", hotelID);
        command.Parameters.AddWithValue("@numRoom", numRoom);
        command.Parameters.AddWithValue("@departDate", departDate);
        command.Parameters.AddWithValue("@returnDate", returnDate);
        command.Parameters.AddWithValue("@intervalTime", intervalTime);

        int n = int.Parse(command.ExecuteScalar().ToString());
        cnn.Close();

        if (n > 0)
            return true;
        else
            return false;
    }

    public static bool EditTourHotel(int hotelID, int numRoom, DateTime departDate, DateTime returnDate, float intervalTime, int oldTourID, int oldHotelID)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        string sqlQuery = "UPDATE Tour_Hotel Set hotelID =@hotelID,numRoom=@numRoom,departDate=@departDate,returnDate=@returnDate,intervalTime=@intervalTime  WHERE tourID=@oldTourID AND hotelID=@oldHotelID";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@hotelID", hotelID);
        command.Parameters.AddWithValue("@numRoom", numRoom);
        command.Parameters.AddWithValue("@departDate", departDate);
        command.Parameters.AddWithValue("@returnDate", returnDate);
        command.Parameters.AddWithValue("@intervalTime", intervalTime);
        command.Parameters.AddWithValue("@oldTourID", oldTourID);
        command.Parameters.AddWithValue("@oldHotelID", oldHotelID);

        int n = command.ExecuteNonQuery();

        cnn.Close();

        if (n == 1)
            return true;
        else
            return false;
    }

    public static bool DeleteTourHotel(int tourID, int hotelID)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        string sqlQuery = "DELETE Tour_Hotel WHERE tourID=@tourID AND hotelID=@hotelID";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@tourID", tourID);
        command.Parameters.AddWithValue("@hotelID", hotelID);

        int n = command.ExecuteNonQuery();

        if (n == 0)
            return false;
        else
            return true;
    }
    public static bool DeleteTourHotelFromTour(int tourID)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        string sqlQuery = "DELETE Tour_Hotel WHERE tourID=@tourID";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@tourID", tourID);

        int n = command.ExecuteNonQuery();

        if (n == 0)
            return false;
        else
            return true;
    }
    public static bool DeleteTourHotelFromHotel(int hotelID)
    {
        string strCon = WebConfigurationManager.ConnectionStrings[Common.CONNECT_STRING].ConnectionString;
        SqlConnection cnn = new SqlConnection(strCon);
        cnn.Open();

        string sqlQuery = "DELETE Tour_Hotel WHERE hotelID=@hotelID";
        SqlCommand command = new SqlCommand(sqlQuery, cnn);
        command.Parameters.AddWithValue("@hotelID", hotelID);

        int n = command.ExecuteNonQuery();

        if (n == 0)
            return false;
        else
            return true;
    }
}